CREATE TABLE [dbo].[RentProjectionUnit](
	[RentProjectionUnitId] [int] IDENTITY(1,1) NOT NULL,
	[Apn] [varchar](50) NOT NULL,
	[UnitNumber] [varchar](50) NULL,
	[SquareFootage] [float] NULL,
	[Bedrooms] [int] NULL,
	[Bathrooms] [varchar](50) NULL,
	[Rehab] [money] NULL,
 CONSTRAINT [PK_RentProjectionUnit] PRIMARY KEY CLUSTERED 
(
	[RentProjectionUnitId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[RentProjectionUnit]  WITH CHECK ADD  CONSTRAINT [FK_RentProjectionUnit_RentProjection] FOREIGN KEY([Apn])
REFERENCES [dbo].[RentProjection] ([Apn])
GO
ALTER TABLE [dbo].[RentProjectionUnit] CHECK CONSTRAINT [FK_RentProjectionUnit_RentProjection]
GO
create PROCEDURE getRentProjectionUnit
(
    @Apn          varchar(50)
)

AS
begin
	SET NOCOUNT ON
	
	select RentProjectionUnit.RentProjectionUnitId, RentProjectionUnit.Apn, RentProjectionUnit.UnitNumber, 
		RentProjectionUnit.SquareFootage, RentProjectionUnit.Bedrooms, RentProjectionUnit.Bathrooms, RentProjectionUnit.Rehab,
		RentProjection.PropertyLocation
	from RentProjectionUnit left join RentProjection on RentProjectionUnit.Apn = RentProjection.Apn
	where RentProjectionUnit.Apn = @Apn
	order by RentProjectionUnit.UnitNumber
end	
go
create PROCEDURE insertRentProjectionUnit
(
    @Apn varchar(50),
	@UnitNumber varchar(50) = null,
	@SquareFootage float = null,
	@Bedrooms int = null,
	@Bathrooms varchar(50) = null,
	@Rehab money = null
)

AS
begin
	SET NOCOUNT ON
	
	insert into RentProjectionUnit
	(Apn, UnitNumber, SquareFootage, Bedrooms, Bathrooms, Rehab)
	values (@Apn, @UnitNumber, @SquareFootage, @Bedrooms, @Bathrooms, @Rehab)
end	
go
create PROCEDURE updateRentProjectionUnit
(
    @RentProjectionUnitId int,
	@UnitNumber varchar(50) = null,
	@SquareFootage float = null,
	@Bedrooms int = null,
	@Bathrooms varchar(50) = null,
	@Rehab money = null
)

AS
begin
	SET NOCOUNT ON
	
	update RentProjectionUnit
	set UnitNumber = @UnitNumber, SquareFootage = @SquareFootage, Bedrooms = @Bedrooms, Bathrooms = @Bathrooms, Rehab = @Rehab
	where (RentProjectionUnitId = @RentProjectionUnitId)
end
go
create PROCEDURE deleteRentProjectionUnit
(
    @RentProjectionUnitId int
)

AS
BEGIN
	SET NOCOUNT ON

	delete 
	from RentProjectionUnit
    where RentProjectionUnitId = @RentProjectionUnitId
END	
go